Excel BI - Excel Challenge 812

excel-challenges
excel-formulas
🔰 Sum of Value Generate the Sum of Value and Running % of Value for year brackets of 5 years each.
Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 812

Challenge Description

🔰 Sum of Value Generate the Sum of Value and Running % of Value for year brackets of 5 years each.

Solutions

library(tidyverse)
library(readxl)

path = "Excel/800-899/812/812 Generate Pivot Table.xlsx"
input = read_excel(path, range = "A1:B100")
test  = read_excel(path, range = "D2:F10")

result = input %>%
  mutate(Year = cut(Year, breaks=seq(1990,2025,5), right=FALSE, labels=paste(seq(1990,2020,5), seq(1994,2024,5), sep='-'))) %>%
  group_by(Year) %>%
  summarise(Total = sum(Value), .groups='drop') %>%
  mutate(Running = cumsum(Total)/sum(Total)) %>%
  add_row(Year = 'Grand Total', Total = sum(.$Total), Running = max(.$Running))
colnames(result) = colnames(test)

all.equal(result, test)
# TRUE
  • Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Aggregate or rank the data at the required grouping level; Reshape the result into the workbook output format.
  • Strengths: The transformation is organized around the correct grouping level, which keeps the business logic clear.
  • Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
  • Gem: The key move is solving the problem at the right grain before shaping the final output.
import pandas as pd

path = "800-899/812/812 Generate Pivot Table.xlsx"
df = pd.read_excel(path, usecols="A:B", nrows=100)
test = pd.read_excel(path, usecols="D:F", skiprows=1, nrows=8)

bins = [1990,1995,2000,2005,2010,2015,2020,2025]
labels = [f"{y}-{y+4}" for y in range(1990,2020,5)] + ["2020-2024"]
df['Year'] = pd.cut(df['Year'], bins=bins, labels=labels, right=False).astype(str).fillna(df['Year'].astype(str))
result = df.groupby('Year', as_index=False)['Value'].sum().rename(columns={'Value':'Total'})
result['Running'] = result['Total'].cumsum() / result['Total'].sum()

grand_total = pd.DataFrame([{'Year': 'Grand Total', 'Total': result['Total'].sum(), 'Running': 1.000}])
result = pd.concat([result, grand_total], ignore_index=True)
result.columns = test.columns

print(result.equals(test)) # True

The Python version follows the same grouped logic and keeps the transformation explicit in a dataframe pipeline.

Difficulty Level

Medium

The individual steps are manageable, but the correct transformation pattern is not obvious from the raw data.